IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetValueTableOfDelimittedString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetValueTableOfDelimittedString]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 
/******************************************************************          
* Name:  [GetValueTableOfDelimittedString]       
* Purpose:  Returns a table output from the string containing the delimited values          
*          
* PARAMETERS          
* Name(Input)        Description               
* -------------    -------------------------------------------          
* @text              Delimited string that needs to be processed          
* @delimchar   Delimitting character          
          
* PARAMETERS          
* Name(Output)        Description               
* -------------    -------------------------------------------          
* @Table            return the table constructed from the Delimitted string          
******************************************************************/          
      
CREATE FUNCTION [GetValueTableOfDelimittedString]       
       (      
    @text varchar(max),      
    @delimchar varchar(20)= ''      
       )      
RETURNS @Table TABLE ([Id] int IDENTITY(1, 1) NOT NULL,      
                      [Value]  varchar(100) NOT NULL)       
AS      
BEGIN      
      
  DECLARE @position      int,      
          @textPosition  int,      
          @DelimittedStringLength int,      
          @str      varchar(max),      
          @tmpstr   varchar(max),      
          @leftover varchar(max)        
      
  SET @textPosition = 1      
  SET @leftover = ''      
      
  WHILE @textPosition <= datalength(@text)     
  BEGIN      
      
     SET @DelimittedStringLength = datalength(@text) - datalength(@leftover)      
     SET @tmpstr = ltrim(@leftover + substring(@text, @textPosition, @DelimittedStringLength))      
     SET @textPosition = @textPosition + @DelimittedStringLength      
      
 SET @position = charindex(@delimchar, @tmpstr)      
      
    WHILE @position > 0      
     BEGIN      
       SET @str = substring(@tmpstr, 1, @position - 1)      
       INSERT @Table (Value) VALUES (@str)      
       SET @tmpstr = ltrim(substring(@tmpstr, @position + 1, len(@tmpstr)))      
       SET @position = charindex(@delimchar, @tmpstr)      
     END      
      
 SET @leftover = @tmpstr      
  END      
      
IF ltrim(rtrim(@leftover)) <> ''      
    INSERT @Table (Value) VALUES (@leftover)      
RETURN      
      
END 